﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ExcelInsertImage
{
    public partial class FormExcelInsertImage : Form
    {
        public FormExcelInsertImage()
        {
            InitializeComponent();

            var files = Directory.GetFiles($"{System.AppDomain.CurrentDomain.BaseDirectory}TempPic");
            foreach(var path in files)
            {
                File.Delete(path.ToString());

            }

            flowLayoutPanel1.AllowDrop = true;
        }

        private bool DatToPng(string filePath,ref string path)
        {
            FileStream fileStream = new FileStream(filePath.ToString(), FileMode.Open, FileAccess.Read);
            BinaryReader binaryReader = new BinaryReader(fileStream);
            ushort num = (ushort)binaryReader.ReadInt16();
            string text = $"{System.AppDomain.CurrentDomain.BaseDirectory}/TempPic/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".png";
            string output = text.Replace("dat", "png");
            ushort num2 = (ushort)((num >> 8 | (int)num << 8) ^ 35152);
            binaryReader.Close();
            string text2 = num2.ToString("x4");
            string text3 = text2.Substring(0, 2);
            string b = text2.Substring(2, 2);
            char d = (char)Convert.ToInt32(text3, 16);
            bool flag = text3 == b;
            if (flag)
            {
                FileStream newfileStream = new FileStream(filePath.ToString(), FileMode.Open);
                byte[] array = new byte[newfileStream.Length];
                byte[] array2 = new byte[newfileStream.Length];
                newfileStream.Seek(0L, SeekOrigin.Begin);
                newfileStream.Read(array, 0, (int)newfileStream.Length);
                newfileStream.Close();
                newfileStream.Dispose();
                for (int j = 0; j < array.Length; j++)
                {
                    array2[j] = (byte)((char)array[j] ^ d);
                }
                using (FileStream fileStream2 = new FileStream(output, FileMode.Append))
                {
                    fileStream2.Write(array2, 0, array2.Length);
                }
                path = text;
            }
            return flag;
        }

        private bool DatToJpg(string filePath, ref string path)
        {
            FileStream fileStream = new FileStream(filePath.ToString(), FileMode.Open, FileAccess.Read);
            BinaryReader binaryReader = new BinaryReader(fileStream);
            ushort num = (ushort)binaryReader.ReadInt16();
            string text = $"{System.AppDomain.CurrentDomain.BaseDirectory}/TempPic/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".jpg";
            string output = text.Replace("dat", "jpg");
            ushort num2 = (ushort)((num >> 8 | (int)num << 8) ^ 65496);
            binaryReader.Close();
            string text2 = num2.ToString("x4");
            string text3 = text2.Substring(0, 2);
            string b = text2.Substring(2, 2);
            char d = (char)Convert.ToInt32(text3, 16);
            bool flag = text3 == b;
            if (flag)
            {
                FileStream newfileStream = new FileStream(filePath.ToString(), FileMode.Open);
                byte[] array = new byte[newfileStream.Length];
                byte[] array2 = new byte[newfileStream.Length];
                newfileStream.Seek(0L, SeekOrigin.Begin);
                newfileStream.Read(array, 0, (int)newfileStream.Length);
                newfileStream.Close();
                newfileStream.Dispose();
                for (int j = 0; j < array.Length; j++)
                {
                    array2[j] = (byte)((char)array[j] ^ d);
                }
                using (FileStream fileStream2 = new FileStream(output, FileMode.Append))
                {
                    fileStream2.Write(array2, 0, array2.Length);
                }
                path = text;
            }
            return flag;
        }

        private void flowLayoutPanel1_DragDrop(object sender, DragEventArgs e)
        {
            foreach (var filePath in (System.Array)e.Data.GetData(DataFormats.FileDrop))
            {
                string path = filePath.ToString();
                if (filePath.ToString().Contains(".dat"))
                {
                    DatToPng(filePath.ToString(), ref path);
                    DatToJpg(filePath.ToString(), ref path);
                }
                PictureBox pictureBox = new PictureBox();
                pictureBox.Size = new Size(150, 150);
                pictureBox.SizeMode = PictureBoxSizeMode.Zoom;
                pictureBox.Load(path);
                flowLayoutPanel1.Controls.Add(pictureBox);
            }
        }

        private void flowLayoutPanel1_DragEnter(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(DataFormats.FileDrop))
                e.Effect = DragDropEffects.Link;
            else e.Effect = DragDropEffects.None;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl files (*.xlsx)|*.xlsx";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel File";
            saveFileDialog.ShowDialog();
            if (saveFileDialog.FileName == "")
                return;

            string localFilePath = saveFileDialog.FileName.ToString();
            DataTable dt = new DataTable();
            dt.Columns.Add("图片", typeof(string));
            if (SaveExcel(dt, localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1), saveFileDialog.FileName.ToString()))
            {
                MessageBox.Show("导出成功");
            }
            else
            {
                MessageBox.Show("导出失败");
            }


            //foreach(var pic in flowLayoutPanel1.Controls)
            //{
            //    PictureBox pictureBox = (PictureBox)pic;
            //    Console.WriteLine(pictureBox.ImageLocation);
            //}
        }


        /// 将图片插入到指定的单元格位置，并设置图片的宽度和高度。
        /// 注意：图片必须是绝对物理路径
        /// </summary>
        /// <param name="RangeName">单元格名称，例如：B4</param>
        /// <param name="PicturePath">要插入图片的绝对路径。</param>
        public void InsertPicture(string RangeName, Microsoft.Office.Interop.Excel._Worksheet sheet, string PicturePath)
        {
            Image image = Image.FromFile(PicturePath);
            float picWidth = image.Width / (image.Height / 150);
            Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(RangeName, Type.Missing);
            rng.Select();
            rng.RowHeight = 150;
            rng.ColumnWidth = 25;
            float PicLeft, PicTop, PicWidth, PicHeight;    //距离左边距离，顶部距离，图片宽度、高度
            PicTop = Convert.ToSingle(rng.Top);
            PicWidth = Convert.ToSingle(rng.MergeArea.Width);
            PicHeight = Convert.ToSingle(rng.Height);
            PicWidth = Convert.ToSingle(rng.Width);
            PicLeft = Convert.ToSingle(rng.Left);//+ (Convert.ToSingle(rng.MergeArea.Width) - PicWidth) / 2;

            sheet.Shapes.AddPicture2(PicturePath, Microsoft.Office.Core.MsoTriState.msoCTrue,
 Microsoft.Office.Core.MsoTriState.msoTrue, 0, PicTop, picWidth, 150, Microsoft.Office.Core.MsoPictureCompress.msoPictureCompressFalse);
        }


        private bool SaveExcel(DataTable excelTable, string filename, string saveFilePath)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                string filePath = System.AppDomain.CurrentDomain.BaseDirectory+ "不压缩的模板.xlsx";
                filename = "选版";
                Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(filePath);
                Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Sheets[1];
                wSheet.Name = filename;

                object[,] objData = new object[excelTable.Rows.Count + 1, excelTable.Columns.Count];
                //首先将数据写入到一个二维数组中  
                for (int i = 0; i < excelTable.Columns.Count; i++)
                {
                    objData[0, i] = excelTable.Columns[i].ColumnName;
                }
                if (excelTable.Rows.Count > 0)
                {
                    for (int i = 0; i < excelTable.Rows.Count; i++)
                    {
                        for (int j = 0; j < excelTable.Columns.Count; j++)
                        {
                            if (excelTable.Rows[i][j].Equals(float.NaN))//查询过来的float.NaN
                                objData[i + 1, j] = "-";
                            //else if (String.IsNullOrEmpty(excelTable.Rows[i][j].ToString()) && excelTable.Rows[i][j].Equals(DBNull.Value))//有dbnull的数据，需要屏蔽掉——在数据源处理了
                            //    objData[i + 1, j] = excelTable.Rows[i][j];
                            else
                                objData[i + 1, j] = excelTable.Rows[i][j];
                        }
                    }
                }
                string startCol = "A";//这里关键，计算要替换的区域
                int iCnt = ((excelTable.Columns.Count - 1) / 26);//当列数是26时 不-1 会出现问题，自己试试就明白了
                string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
                string endCol = endColSignal + ((char)('A' + excelTable.Columns.Count - iCnt * 26 - 1)).ToString();
                Microsoft.Office.Interop.Excel.Range range = wSheet.get_Range(startCol + "1", endCol + (excelTable.Rows.Count + 1).ToString());

                range.Value = objData; //给Exccel中的Range整体赋值  
                range.EntireColumn.AutoFit(); //设定Excel列宽度自适应  
                wSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1;//Excel文件列名 字体设定为Bold  

                for (int i = 2; i < flowLayoutPanel1.Controls.Count + 2; i++)
                {
                    InsertPicture("A" + i.ToString(), wSheet, ((PictureBox)flowLayoutPanel1.Controls[i - 2]).ImageLocation);

                }

                //设置禁止弹出保存和覆盖的询问提示框   
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿 
                wBook.Save();
                wSheet.SaveAs(saveFilePath);
                wBook.Close();
                //保存excel文件   
                //app.Save(filePath);
                //app.SaveWorkspace(filePath);
                app.Quit();
                GC.Collect();
                return true;
            }
            catch (Exception err)//这里还有些问题，比如 对方安装的是WPS 不会提示中文错误，没有安装office 也不会弹出该错误
            {
                MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                //强制结束excel进程
                IntPtr t = new IntPtr(app.Hwnd);
                int k = 0;
                //GetWindowThreadProcessId(t, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();
                return false;
            }
            finally
            {


            }
        }
    }
}
